/**
 * Copyright (c) 2007,中企动力华南研发中心<br>
 * All rights reserved.
 */
package com.ce.pms.renovation.dao.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.pms.common.model.SysrefcodeDO;
import com.ce.pms.common.util.SqlConfig;
import com.ce.pms.renovation.dao.IRenovateRequisitionDao;
import com.ce.pms.renovation.model.PassDO;
import com.ce.pms.renovation.model.RenovateRequisitionDO;
import com.ce.pms.renovation.vo.RenovateRequisitionQueryVO;
import com.ce.pms.renovation.vo.RenovateRequisitionVO;
import com.ce.util.BlankUtil;
import com.ce.util.DateUtil;
import com.sitechasia.webx.core.support.Page;

/**
 * 功能描述：装修申请持久层<br>
 * @author 罗军林 创建日期：2008-12-22
 */
public class RenovateRequisitionDaoImpl extends PmsBaseDaoImpl<RenovateRequisitionDO> implements IRenovateRequisitionDao {
	private final String QUERY_REQUISITIONS="queryRequisitions";
	private final String GET_MAX_REQUISTION_NUMBER="getMaxRequistionNumber";
	private SqlConfig sqlconfig=null;
	public RenovateRequisitionDaoImpl(){
		sqlconfig=new SqlConfig("RenovateSQL.xml");
	}

	public Page queryRequisitions(Long buildingId, String houseNumber,String state, int pageSize, int pageNumber) {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * 功能描述:得到指定房间没有完结（包括正常完结和异常完结）的装修申请<br>
	 * @param houseId
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-24<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public RenovateRequisitionDO getRenovateRequisitionDOByHouseId(Long houseId){
		String hql="FROM RenovateRequisitionDO re WHERE re.house.id=? AND re.processState!=? AND re.processState!=?)";
		Query query=createQuery(hql, houseId,SysrefcodeDO.RM_RENOVATE_PROCESS_STATE_NOR,SysrefcodeDO.RM_RENOVATE_PROCESS_STATE_EXP);
		return (RenovateRequisitionDO)query.uniqueResult();
	}

	/**
	 * 功能描述:getMaxRequistionNumber<br>
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2009-1-4<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public String getMaxRequistionNumber() {
		String sql=sqlconfig.getSql(GET_MAX_REQUISTION_NUMBER);
		Query query = super.createSQLQuery(sql,null);
		Object obj = query.uniqueResult();
		if(BlankUtil.isBlank(obj))
			return null;
		String count = (String) obj;
		return count;
	}

	/**
	 * 功能描述:装修申请查询<br>
	 * @param queryCondition
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-23<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public Page queryRequisitions(RenovateRequisitionQueryVO queryCondition) {
		Map<String, Object> parameter=new HashMap<String, Object>();
		StringBuilder sql=new StringBuilder(sqlconfig.getSql(QUERY_REQUISITIONS));
		sql.append(buildCondition(parameter,queryCondition));
		sql.append(buildOrder(queryCondition));
		Page page= pagedSqlNameQueryByPageNo(sql.toString(), queryCondition.getPageNo(), queryCondition.getPageSize(), parameter);
		page.setResult( assembleRequisitionO2V(page.getResult()) );
		return page;
	}

	/**
	 * 功能描述:将原生SQL查询的申请对象数组转成申请VO<br>
	 * @param result
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-23<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private List<RenovateRequisitionVO> assembleRequisitionO2V(List<Object[]> result) {
		List<RenovateRequisitionVO> temp=new ArrayList<RenovateRequisitionVO>();
		for(Object[] item : result){
			RenovateRequisitionVO renovate=new RenovateRequisitionVO();
			renovate.setKeyword( ((BigInteger)item[0]).longValue() );//装修申请主键
			renovate.setRequisitionNumber((String)item[1]);//申请单号
			renovate.setHouseNumber( (String)item[2] );//房号
			renovate.setClientNames( (String)item[3] );//业务姓名
			renovate.setRequisitionDate( (Date)item[4] );//申请日期
			renovate.setBeginDate( (Date)item[5] );//施工开始日期
			renovate.setEndDate( (Date)item[6] );//施工结束日期
			renovate.setRequisitionState( (String)item[7] );//审批状态
			renovate.setFeeState( (String)item[8] );//费用状态
			renovate.setRenovateLicenceState( (String)item[9] );//许可证状态
			renovate.setCheckConfirmYn( (String)item[10] );//进场确认
			renovate.setProcessState( (String)item[11]);//装修进程
			temp.add(renovate);
		}
		return temp;
	}

	/**
	 * 功能描述:构建查询条件<br>
	 * @param parameter
	 * @param queryCondition
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-23<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private String buildCondition(final Map<String, Object> parameter,final RenovateRequisitionQueryVO queryCondition){
		StringBuilder str=new StringBuilder();
		//楼栋
		if(queryCondition.getBuildingId()!=null){
			str.append(" and HB.BUILDING_ID=:buildingId ");
			parameter.put("buildingId", queryCondition.getBuildingId());
		}

		//房号
		if(!BlankUtil.isBlank(queryCondition.getHouseNumber())){
			str.append(" and HM.HOUSE_NUMBER like :houseNumber ");
			parameter.put("houseNumber", "%" + queryCondition.getHouseNumber().trim()+"%" );
		}

		//审批状态
		if(!BlankUtil.isBlank(queryCondition.getRequisitionState())){
			str.append(" and RE.REQUISITION_STATE=:requisitionState ");
			parameter.put("requisitionState", queryCondition.getRequisitionState().trim());
		}

		//客户姓名
		if(!BlankUtil.isBlank(queryCondition.getClientNames())){
			str.append(" and RE.CLIENT_NAMES like :clientNames ");
			parameter.put("clientNames", "%"+queryCondition.getClientNames().trim()+"%");
		}

		//申请号
		if(!BlankUtil.isBlank(queryCondition.getRequisitionNumber())){
			str.append(" and RE.REQUISITION_NUMBER like :requisitionNumber ");
			parameter.put("requisitionNumber", "%"+queryCondition.getRequisitionNumber().trim()+"%");
		}

		//记录人
		if(!BlankUtil.isBlank(queryCondition.getCreatorName())){
			str.append(" and EMP.EMPLOYEE_NAME like :creatorName ");
			parameter.put("creatorName", "%"+queryCondition.getCreatorName().trim()+"%");
		}

		//费用状态
		if(!BlankUtil.isBlank(queryCondition.getChargeState())){
			str.append(" and RE.FEE_STATE=:feeState ");
			parameter.put("feeState", queryCondition.getChargeState());
		}

		//许可证状态
		if(!BlankUtil.isBlank(queryCondition.getLicenceState())){
			str.append(" and ( LIC.STATE =:licenceState ");
			if(SysrefcodeDO.RM_RENOVATE_LICENCE_STATE_NO.equals(queryCondition.getLicenceState())){
				str.append(" or LIC.STATE IS NULL ");
			}
			str.append(") ");
			parameter.put("licenceState", queryCondition.getLicenceState().trim());
		}

		//记录开始时间
		if(null != queryCondition.getAnnalBegin()){
			str.append(" and RE.SYS_DATE >=:annalBegin ");
			parameter.put("annalBegin", queryCondition.getAnnalBegin());
		}

		//记录结束时间
		if(null != queryCondition.getAnnalEnd()){
			Date date=DateUtil.addDate(queryCondition.getAnnalEnd(), 1);
			str.append(" and RE.SYS_DATE < ADDDATE(:annalEnd,1) ");
			parameter.put("annalEnd", date);
		}

		//装修申请开始时间
		if(null != queryCondition.getRequisitionBegin()){
			str.append(" and RE.REQUISITION_DATE >=:requisitionBegin ");
			parameter.put("requisitionBegin", queryCondition.getRequisitionBegin());
		}

		//装修申请结束时间
		if(null != queryCondition.getRequisitionEnd()){
			Date date=DateUtil.addDate(queryCondition.getRequisitionEnd(), 1);
			str.append(" and RE.REQUISITION_DATE < ADDDATE(:requisitionEnd,1) ");
			parameter.put("requisitionEnd", date);
		}

		//装修施工开始日期
		if(null != queryCondition.getRenovateBegin()){
			str.append(" and RE.BEGIN_DATE >=:renovateBegin ");
			parameter.put("renovateBegin", queryCondition.getRenovateBegin());
		}

		//装修施工结束日期
		if(null != queryCondition.getRenovateEnd()){
			Date date=DateUtil.addDate(queryCondition.getRenovateEnd(), 1);
			str.append(" and RE.END_DATE < ADDDATE(:renovateEnd,1) ");
			parameter.put("renovateEnd", date);
		}

		return str.toString();
	}

	/**
	 * 功能描述:<br>
	 * @param orderStr
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-23<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private String buildOrder(RenovateRequisitionQueryVO queryCondition){
		if(null==queryCondition.getOrderValue()){
			return " ORDER BY RE.SYS_DATE DESC ";
		}else if(queryCondition.getOrderValue().equalsIgnoreCase("houseNumber")
				&&  queryCondition.getOrderKey().equalsIgnoreCase("asc")){
			return " ORDER BY HB.SEQ_NO ASC,FL.STOREY_NUMBER ASC,HM.HOUSE_NUMBER ASC";
		}else if(queryCondition.getOrderValue().equalsIgnoreCase("houseNumber")
				&&  queryCondition.getOrderKey().equalsIgnoreCase("desc")){
			return " ORDER BY HB.SEQ_NO DESC,FL.STOREY_NUMBER DESC,HM.HOUSE_NUMBER DESC";
		}else if(queryCondition.getOrderValue().equalsIgnoreCase("requisitionDate")){
			return " ORDER BY RE.REQUISITION_DATE ".concat(queryCondition.getOrderKey());
		}else if(queryCondition.getOrderValue().equalsIgnoreCase("requisitionNumber")){
			return " ORDER BY RE.REQUISITION_NUMBER ".concat(queryCondition.getOrderKey());
		}
		else{
			return " ORDER BY RE.SYS_DATE DESC ";
		}
	}

}
